package com.macro.mall.product.dto;

import com.alibaba.fastjson.JSONArray;
import lombok.extern.slf4j.Slf4j;

@Slf4j
public class PmStockDoorSql {
    /**
     * 获取上门服务数据库SQL
     */
    public  static String getStockCountSqlByDoor(JSONArray salePrptyArray, JSONArray naturePrptyArray, String prodSpecType, Long prodSpecId, String provCode, String cityCode, String countyCode, String today, String endDay){
        StringBuffer sql = new StringBuffer();

        // sql.append(" select pk.channel_id,pk.prod_id,pk.prod_spec_id,pk.sku_inst_id,pk.stock_date,pk.stock_time,pk.total_stock,pk.current_stock,pk.prov_code,pk.city_code,pk.county_code ");
        sql.append( "  select pk.stock_date,pk.stock_time,sum(current_stock) as stock_count " );
        sql.append( "  from pm_prod_stock pk, (" );
        sql.append(PmStockDoorSql.getSpecChannelByDoor(salePrptyArray,naturePrptyArray,prodSpecId, provCode, cityCode,  countyCode));
        sql.append( "  ) A " );
        sql.append( "  where pk.type=12 and pk.channel_id = A.deptId and  pk.prod_id=A.prodId and pk.sku_inst_id = A.skuInstId  " );
       // sql.append( "   and pk.prov_code=A.prov_code and pk.city_code=A.city_code and pk.county_code=A.county_code  " );

        sql.append( " AND pk.prov_code = A.prov_code " );
        sql.append( " AND ((pk.city_code is null  and A.city_code is null)  or   pk.city_code = A.city_code) " );
        sql.append( " AND ((pk.county_code is null  and A.county_code is null) or pk.county_code = A.county_code) " );
        sql.append("   and pk.stock_date>='"+today+"' and pk.stock_date<='"+endDay+"'" );
        sql.append( "   group by pk.stock_date,pk.stock_time  " );

        log.debug("★★★★★★★★★★★★★★★★getStockSqlByDoor=" + sql.toString());

        return sql.toString();

//        select pk.sku_inst_id,pk.stock_date,pk.stock_time,sum(current_stock) as stock_count
//        from pm_prod_stock pk,
//                (select ps.id as deptId,ps.dept_name as deptName,p.id as prodId   ,pr0.sku_inst_id as skuInstId , pa.prov_code,pa.city_code,pa.county_code
//        from pm_product p,pm_prod_channel pc,ums_service_dept ps ,pm_prod_service_type pt ,pm_prod_release_area pa,
//                pm_prod_price_plan_prpty_rel pr0,pm_prod_price_plan_prpty_rel pr1
//        where p.id=pc.prod_id and pc.prod_id and p.`status`=0 and pc.prod_spec_id=1 and p.id=pt.prod_id and pt.service_type=2
//        and pc.channel_id=ps.id and ps.`status`='01'
//        and p.id=pa.prod_id and pa.prov_code='110000' and (pa.city_code='110100' or pa.city_code is null )and ( pa.county_code='110114' or pa.county_code is null)
//        and pr0.prod_id=p.id and pr0.prpty_code='1'
//        and pr0.prpty_val_code='1' and pr1.prod_id=p.id and pr1.prpty_code='2'
//        and pr1.prpty_val_code='5' and pr1.sku_inst_id=pr0.sku_inst_id
//)A
//        where pk.type=12 and pk.channel_id = A.deptId and  pk.prod_id=A.prodId and pk.sku_inst_id = A.skuInstId
//        and pk.prov_code=A.prov_code and pk.city_code=A.city_code and pk.county_code=A.county_code
//        group by pk.sku_inst_id,pk.stock_date,pk.stock_time

    }
    /**
     * 获取上门服务的服务机构
     */
    public static String getSpecChannelByDoor(JSONArray salePrptyArray, JSONArray naturePrptyArray, Long prodSpecId, String provCode, String cityCode, String countyCode)
    {
        StringBuffer sql = new StringBuffer();
        sql.append(" select ps.id as deptId,ps.dept_name as deptName,p.id as prodId , pa.prov_code,pa.city_code,pa.county_code  ");
        if (salePrptyArray != null && salePrptyArray.size() > 0) {
            sql.append(" ,pr0.sku_inst_id as skuInstId ");
        }
        sql.append(" from  pm_product p,pm_prod_channel pc,ums_service_dept ps ,pm_prod_service_type pt ,pm_prod_release_area pa ");
        if (naturePrptyArray != null && naturePrptyArray.size() > 0) {
            for (int i = 0; i < naturePrptyArray.size(); i++) {
                sql.append(",pm_prod_prpty_val pv" + i);
            }
        }
        if (salePrptyArray != null && salePrptyArray.size() > 0) {
            for (int i = 0; i < salePrptyArray.size(); i++) {
                sql.append(",pm_prod_price_plan_prpty_rel pr" + i);
            }
        }
        sql.append(" where p.id=pc.prod_id and p.`status`=0 and p.prod_spec_id=" + prodSpecId);
        sql.append(" and pc.channel_id=ps.id and ps.`status`='02'  and p.id=pt.prod_id and pt.service_type=2 ");
        sql.append(" and p.id=pa.prod_id " );
        sql.append("   and pa.prov_code='"+provCode+"' and (pa.city_code='"+cityCode+"' or pa.city_code is null )and ( pa.county_code='"+countyCode+"' or pa.county_code is null) ");

        if (naturePrptyArray != null && naturePrptyArray.size() > 0) {
            for (int i = 0; i < naturePrptyArray.size(); i++) {
                sql.append(" and pv" + i + ".prod_id=p.id and pv" + i + ".prpty_code='" + naturePrptyArray.getJSONObject(i).getString("prptyCode") + "' and pv" + i + ".prpty_val_code='" + naturePrptyArray.getJSONObject(i).getString("prptyValCode") + "'");
            }
        }
        if (salePrptyArray != null && salePrptyArray.size() > 0) {
            for (int i = 0; i < salePrptyArray.size(); i++) {
                sql.append(" and pr" + i + ".prod_id=p.id and pr" + i + ".prpty_code='" + salePrptyArray.getJSONObject(i).getString("prptyCode") + "' and pr" + i + ".prpty_val_code='" + salePrptyArray.getJSONObject(i).getString("prptyValCode") + "'");
            }
        }
        if (salePrptyArray != null && salePrptyArray.size() > 1) {
            for (int i = 1; i < salePrptyArray.size(); i++) {
                sql.append(" and pr" + i + ".sku_inst_id=pr0.sku_inst_id");
            }
        }
        log.debug("★★★★★★★★★★★★★★★★getSpecChannelByDoor =" + sql.toString());

        return sql.toString();
    }
    /**
     * 获取上门服务可扣减库存集合
     */
    public  static String getStockSqlByDoor(JSONArray salePrptyArray, JSONArray naturePrptyArray, Long prodSpecId, String provCode, String cityCode, String countyCode, String stockDate,String stockTime) {
        StringBuffer sql = new StringBuffer();

        sql.append( "  select pk.id,pk.stock_date,pk.stock_time,pk.current_stock  " );
        sql.append( "  from pm_prod_stock  pk, pm_stock_deduct_rule pd, (  " );

        sql.append(PmStockDoorSql.getSpecChannelByDoor(salePrptyArray,naturePrptyArray,prodSpecId, provCode, cityCode,  countyCode));
        sql.append( "  ) A " );
        sql.append( "  where pk.type=12 and pk.channel_id = A.deptId and  pk.prod_id=A.prodId and pk.sku_inst_id = A.skuInstId   " );
        sql.append( "  and pk.prov_code=A.prov_code   AND ((pk.city_code is null  and A.city_code is null)  or   pk.city_code = A.city_code) \n" +
                "  AND ((pk.county_code is null  and A.county_code is null) or pk.county_code = A.county_code)  " );
        sql.append( "  and pk.channel_id=pd.channel_id and pd.prov_code =pk.prov_code and (pd.city_code =pk.city_code  or pd.city_code is null ) and  ( pd.county_code=pk.county_code or pd.county_code is null)  " );
        sql.append( "  and  pk.stock_date='"+stockDate+"' and pk.stock_time='"+stockTime+"' and pk.current_stock>0  " );
        sql.append( "  order by pd.seq asc  " );

        log.debug("★★★★★★★★★★★★★★★★getStockSqlByDoor=" + sql.toString());

        return sql.toString();

    }    /**
     * 获取上门服务可扣减库存集合 --无配置规则
     */
    public  static String getStockSqlByDoorNoRule(JSONArray salePrptyArray, JSONArray naturePrptyArray, Long prodSpecId, String provCode, String cityCode, String countyCode, String stockDate,String stockTime) {
        StringBuffer sql = new StringBuffer();

        sql.append( "  select pk.id,pk.stock_date,pk.stock_time,pk.current_stock  " );
        sql.append( "  from pm_prod_stock  pk, (  " );

        sql.append(PmStockDoorSql.getSpecChannelByDoor(salePrptyArray,naturePrptyArray,prodSpecId, provCode, cityCode,  countyCode));
        sql.append( "  ) A " );
        sql.append( "  where pk.type=12 and pk.channel_id = A.deptId and  pk.prod_id=A.prodId and pk.sku_inst_id = A.skuInstId   " );
        sql.append( "  and pk.prov_code=A.prov_code   AND ((pk.city_code is null  and A.city_code is null)  or   pk.city_code = A.city_code) \n" +
                "  AND ((pk.county_code is null  and A.county_code is null) or pk.county_code = A.county_code)  " );
       //  sql.append( "  and pk.channel_id=pd.channel_id and pd.prov_code =pk.prov_code and (pd.city_code =pk.city_code  or pd.city_code is null ) and  ( pd.county_code=pk.county_code or pd.county_code is null)  " );
        sql.append( "  and  pk.stock_date='"+stockDate+"' and pk.stock_time='"+stockTime+"' and pk.current_stock>0 " );
        // sql.append( "  order by pd.seq asc  " );

        log.debug("★★★★★★★★★★★★★★★★getStockSqlByDoor=" + sql.toString());

        return sql.toString();

    }

}


